package projectManagementServer;

import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

import javax.swing.JOptionPane;

public class AccessDatabase {

	Connection con;
	Statement st;
	ResultSet rs;
	
	ArrayList<Member> allMember = new ArrayList<>();
	ArrayList<Project> allProject = new ArrayList<>();
	ArrayList<Project> ongoingProject = new ArrayList<>();
	ArrayList<Project> compProject = new ArrayList<>();
	ArrayList<Files> allFiles = new ArrayList<>();
	ArrayList<MemberProject> allMemProj = new ArrayList<>();
	
	final String allMemberConnection;
	final String allProjectConnection;
	final String allFileConnection;
	final String allMemberProjectConnection;
	
	public AccessDatabase() {
		allMemberConnection = "select * from AllMember";
		allProjectConnection = "select * from AllProject";
		allFileConnection = "select * from AllFile";
		allMemberProjectConnection = "select * from MemberProject";
	}
	
	public void getData(String connection) throws Exception{
		 Connection con = null;
		 String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
	    	Class.forName(driver);
	    		
	    	String db = "jdbc:odbc:data";
	    	con = DriverManager.getConnection(db);
	    		
	    	st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
	    	String sql = connection;
	    	rs = st.executeQuery(sql);
	}	
	
	public ArrayList<Member> getAllMember() throws Exception{	
		getData(allMemberConnection);
		allMember = new ArrayList<>();
		while(rs.next()){
			Member m = new Member(rs.getString("UserName"), rs.getString("Password"), rs.getString("Status"), rs.getString("DateAdded"));
			allMember.add(m);
		}
		return allMember;
	}
	
	public ArrayList<Project> getAllProject() throws Exception{
		getData(allProjectConnection);
		allProject = new ArrayList<>();
		while(rs.next()){
			Project p = new Project(rs.getString("ProjectName"), rs.getString("ProjectLeader"), rs.getString("DateCreated"), rs.getString("Status"));
			allProject.add(p);
		}
		return allProject;
	}
	
	/**public ArrayList<Project> getOngoingProj() throws Exception{
		getData(allProjectConnection);
		
		while(rs.next()){
			//String value = rs.getString("Status");
			//if(value.equals("ONGOING")){
				Project p = new Project(rs.getString("ProjectName"), rs.getString("ProjectLeader"), rs.getString("DateCreated"), rs.getString("Status"));
				ongoingProject.add(p);
			//}
		}
		return ongoingProject;
	}**/
	
	/**public ArrayList<Project> getCompletedProj() throws Exception{
		getData(allProjectConnection);
		
		while(rs.next()){
			//if(rs.getString("Status").equalsIgnoreCase("COMPLETED")){
				Project p = new Project(rs.getString("ProjectName"), rs.getString("ProjectLeader"), rs.getString("DateCreated"), rs.getString("Status"));
				compProject.add(p);
			//}
		}
		return ongoingProject;
	}**/
	
	public ArrayList<Files> getAllFiles() throws Exception{
		getData(allFileConnection);
		allFiles = new ArrayList<>();
		while(rs.next()){
			Files f = new Files(rs.getString("FileName"), rs.getString("FileAuthor"), rs.getBytes("FileContent"), rs.getString("FileType"), rs.getString("DateCreated"), rs.getString("ProjectName"));
			allFiles.add(f);
		}
		return allFiles;
	}
	
	public ArrayList<MemberProject> getAllMemProj() throws Exception{
		getData(allMemberProjectConnection);
		allMemProj = new ArrayList<>();
		while(rs.next()){
			MemberProject mP = new MemberProject(rs.getString("Project"),rs.getString("Member"));
			allMemProj.add(mP);
		}
		return allMemProj;
	}
	
	public void addProject(Project proj) throws Exception{
		getData(allProjectConnection);
		
		rs.moveToInsertRow();
		rs.updateString("ProjectName", proj.getProjectName());
		rs.updateString("ProjectLeader", proj.getProjectLeader());
		rs.updateString("DateCreated", proj.getDateCreated());
		rs.updateString("Status", proj.getStatus());
		rs.insertRow();
	}
	
	public void deleteProject(String proj) throws Exception{
		getData(allProjectConnection);
		
		while(rs.next()){
			if(rs.isAfterLast()){
				rs.last();
			}else{
				if(rs.getString("ProjectName").equalsIgnoreCase(proj)){
					rs.deleteRow();
				}
			}			
		}
	}
	
	public void updateProjectStatusCompleted(String projectName) throws Exception{
		getData(allProjectConnection);
		
		while(rs.next()){
			if(rs.getString("ProjectName").equalsIgnoreCase(projectName)){			
				rs.updateString("Status", "COMPLETED");
				rs.updateRow();
			}
		}
	}
	
	public void updateProjectStatusOngoing(String projectName) throws Exception{
		getData(allProjectConnection);
		
		while(rs.next()){
			if(rs.getString("ProjectName").equalsIgnoreCase(projectName)){			
				rs.updateString("Status", "ONGOING");
				rs.updateRow();
			}
		}
	}
	
	public void addMember(Member mem) throws Exception{
		getData(allMemberConnection);
		
		rs.moveToInsertRow();
		rs.updateString("Username", mem.getUserName());
		rs.updateString("Password", mem.getPassword());
		rs.updateString("Status", mem.getStatus());
		rs.updateString("DateAdded", mem.getDateAdded());
		rs.insertRow();
	}
	
	public void updateMember(Member mem) throws Exception{
		getData(allMemberConnection);
		
		while(rs.next()){
			if(rs.getString("UserName").equalsIgnoreCase(mem.getUserName())){
				rs.updateString("UserName", mem.getUserName());
				rs.updateString("Password", mem.getPassword());
				rs.updateString("Status", mem.getStatus());
				rs.updateRow();
			}			
		}
	}
	
	public void deleteMember(String mem) throws Exception{
		getData(allMemberConnection);
		
		while(rs.next()){
			if(rs.isAfterLast()){
				rs.last();
			}else{
				if(rs.getString("UserName").equalsIgnoreCase(mem)){
					rs.deleteRow();
				}
			}			
		}
	}
	
	public void addFile(String fileName, String fileAuthor, byte[] fileCont, String fileType, String date, String projectName) throws Exception{
		getData(allFileConnection);
		
		rs.moveToInsertRow();
		rs.updateString("FileName", fileName);
		rs.updateString("FileAuthor", fileAuthor);
		rs.updateBytes("FileContent", fileCont);
		rs.updateString("FileType", fileType);
		rs.updateString("DateCreated", date);	
		rs.updateString("ProjectName", projectName);
		rs.insertRow();
	}
	
	public void deleteFile(Files file) throws Exception{
		getData(allFileConnection);
		
		while(rs.next()){
			if(rs.getString("FileName").equalsIgnoreCase(file.getFileName()) && rs.getString("FileAuthor").equalsIgnoreCase(file.getFileAthor()) && rs.getString("DateCreated").equalsIgnoreCase(file.getDateCreated())){
				rs.deleteRow();
			}
		}
	}
	
	public void assignProjectLeader(String project, String userName) throws Exception{
		getData(allProjectConnection);
		
		while(rs.next()){
			if(rs.getString("ProjectName").equalsIgnoreCase(project)){
				rs.updateString("ProjectLeader", userName);
				rs.updateRow();
			}
		}
		
	}
	
	public void addMemberProject(String mem, String project) throws Exception{
		DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
		Date date = new Date();
		getData(allMemberProjectConnection);
		
		rs.moveToInsertRow();
		rs.updateString("Member", mem);
		rs.updateString("Project", project);
		rs.updateString("DateJoin", dateFormat.format(date));
		rs.insertRow();
	}
	
	public void deleteMemberProject(String mem, String project) throws Exception{
		getData(allMemberProjectConnection);
		
		while(rs.next()){
			if(rs.isAfterLast()){
				rs.last();
			}else{
				if(rs.getString("Member").equalsIgnoreCase(mem) && rs.getString("Project").equalsIgnoreCase(project)){
					rs.deleteRow();
				}
			}			
		}
	}
}
